﻿using DTO;
using System;
using System.Collections.Generic;
using System.Linq;

namespace DAL
{
    public partial class CustomerDA : GetData
    {
        CRMEntities db;
        public List<CustomerEntity> LoadCustomerByAll()
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         join s in db.Sources on c.Source_ID equals s.Source_ID
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                         }).ToList();

            return query;
        }

        public CustomerEntity LoadCustomerByID(long CustomerID)
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         where c.Customer_ID == CustomerID
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         join s in db.Sources on c.Source_ID equals s.Source_ID
                         select new CustomerEntity
                         {
                             //main info
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                             //append-dix info
                             ImportDate = s.ImportDate,
                         }).SingleOrDefault();
            return query;
        }

        public CustomerEntity LoadCustomerByHomePhone(string HomePhoneNumber)
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         where c.HomePhoneNumber == HomePhoneNumber
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         join s in db.Sources on c.Source_ID equals s.Source_ID
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                             //append-dix info
                             ImportDate = s.ImportDate,
                         }).SingleOrDefault();

            return query;
        }

        public CustomerEntity LoadCustomerByCellPhone(string CellPhoneNumber)
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         where c.CellPhoneNumber == CellPhoneNumber
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         join s in db.Sources on c.Source_ID equals s.Source_ID
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                             //append-dix info
                             ImportDate = s.ImportDate,
                         }).SingleOrDefault();

            return query;
        }

        public CustomerEntity LoadCustomerByOfficePhone(string OfficePhoneNumber)
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         where c.OfficePhoneNumber == OfficePhoneNumber
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         join s in db.Sources on c.Source_ID equals s.Source_ID
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                             //append-dix info
                             ImportDate = s.ImportDate,
                         }).SingleOrDefault();

            return query;
        }

        public List<CustomerEntity> LoadListCustomerByStatus(int status)
        { 
            db = new CRMEntities();
            var query = (from c in db.Customers
                         where c.CustomerStatus == status
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                         }).ToList();
            return query;
        }

        public List<CustomerEntity> LoadListCustomerByCallStatus(int callstatus)
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         where c.CallStatus == callstatus
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                         }).ToList();
            return query;
        }



    }
}
